﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using Nissen_Model;


using System.Configuration;
using Nissen_Resources;
using ThreadState = System.Threading.ThreadState;
using System.Text.RegularExpressions;

namespace Nissen_Manage.Forms
{
	public partial class ManageFileImport : Form
	{
		public ManageFileImport()
		{
			InitializeComponent();
		}

		private Thread t;
		private void btnSeach_Click(object sender, EventArgs e)
		{
            openFileDialog1.Filter = "txt(*.txt)|*.txt|CSV(*.csv)|*.csv";
			openFileDialog1.FileName = "";
			if (this.openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
			{
				this.txtFilePath.Text = openFileDialog1.FileName;
			}
		}

		private void btnImport_Click(object sender, EventArgs e)
		{
			try
			{
				if (this.txtFilePath.Text.Trim() == "")
				{
					MessageBox.Show(ResourceCommon.GetResource("PleasChangeFile"));
					return;
				}

				ControlPage(true);
				//t = new Thread(delegate() { ImportDataProcess(this.txtFilePath.Text); });
				t = new Thread(delegate() { UserSQLToolsImport(this.txtFilePath.Text); });
				t.Start();
			}
			catch (Exception ex)
			{

				MessageBox.Show(ex.Message);
				ControlPage(false);
			}

		}




		private void ImportDataProcess(string filePath)
		{

			var data = TestReadFile(filePath);

			ImportDataInDB(data);

			this.Invoke(new EventHandler(delegate
			                             	{
			                             		this.ShowMessage(ResourceCommon.GetResource("ImportSuccess"));
			                             		ControlPage(false);
			                             	}));


		}

		/// <summary>
		/// 消息提示
		/// </summary>
		/// <param name="msg"></param>
		public void ShowMessage(string msg)
		{
			MessageBox.Show(msg);
		}

		public void ShowRtxtMsg(string msg)
		{
			this.rtxtMsg.Text = msg;
		}
		private void ControlPage(bool flag)
		{
			if (flag)
			{
				lblLoading.Text = ResourceCommon.GetResource("ImportLoading");
				btnSeach.Enabled = false;
				btnImport.Enabled = false;
                this.button_showtext.Enabled = false;
			}
			else
			{
				lblLoading.Text = "";
				btnSeach.Enabled = true;
				btnImport.Enabled = true;
                this.button_showtext.Enabled = true;
			}
		}
		private IList<GERESULT> TestReadFile(string filePath)
		{
			IList<GERESULT> geresults = new List<GERESULT>();
			try
			{
				
				int i = 0;
				using (StreamReader sr = new StreamReader(filePath))
				{
					String line;
					// Read and display lines from the file until the end of 
					// the file is reached.
					while ((line = sr.ReadLine()) != null)
					{
						if (i == 0)
						{
							i++;
							continue;

						}
						var red = line.Split('	');
						GERESULT geresult = new GERESULT();
						geresult.TESTEE_ID = red[0];
						geresult.SYS_ETY_DTM = DateTime.Parse(red[1]);
						geresult.TEST_CODE = red[2];
						geresult.COURSE_LEVEL_ID = red[3];
						geresult.KYOKA_CD = red[4];
						geresult.UNIT_NO = int.Parse(red[5]);
						geresult.ETY_ID_1 = red[6];
						geresult.RESULT1 = int.Parse(red[7]);
						geresult.ETY_ID_2 = red[8];
						geresult.RESULT2 = int.Parse(red[9]);
						geresult.SYS_UPD_ID = red[10];
						geresult.RESULT = int.Parse(red[11]);

						geresults.Add(geresult);

					}
				}
				
			}
			catch (Exception ex)
			{

				if(Thread.CurrentThread.ThreadState == ThreadState.AbortRequested)
				{
					return null;
					
				}
				this.Invoke(new EventHandler(delegate
				{
					this.ShowMessage(ex.Message);
					ControlPage(false);
				}));
			}
			return geresults;
		}

		private void ImportDataInDB(IList<GERESULT> geresults)
		{
			int i = 0;
			string connectionString = ConfigurationManager.AppSettings["DbHelperConnectionStringReport"];
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				SqlTransaction sqlTransaction = connection.BeginTransaction();
				try
				{
					SqlCommand sqlCommand1 = new SqlCommand();
					sqlCommand1.Connection = connection;
					sqlCommand1.Transaction = sqlTransaction;
					sqlCommand1.CommandText = "DELETE FROM GERESULT";
					sqlCommand1.ExecuteNonQuery();

					foreach (var geresult in geresults)
					{
						i++;
						SqlCommand sqlCommand = new SqlCommand();

						sqlCommand.Connection = connection;
						sqlCommand.Transaction = sqlTransaction;
						sqlCommand.CommandText =
							@"INSERT INTO [GERESULT]
										   ([TESTEE_ID]
										   ,[SYS_ETY_DTM]
										   ,[TEST_CODE]
										   ,[COURSE_LEVEL_ID]
										   ,[KYOKA_CD]
										   ,[UNIT_NO]
										   ,[ETY_ID_1]
										   ,[RESULT1]
										   ,[ETY_ID_2]
										   ,[RESULT2]
										   ,[SYS_UPD_ID]
										   ,[RESULT])
									 VALUES
										   (@TESTEE_ID
										   ,@SYS_ETY_DTM
										   ,@TEST_CODE
										   ,@COURSE_LEVEL_ID
										   ,@KYOKA_CD
										   ,@UNIT_NO
										   ,@ETY_ID_1
										   ,@RESULT1
										   ,@ETY_ID_2
										   ,@RESULT2
										   ,@SYS_UPD_ID
										   ,@RESULT)";



						sqlCommand.Parameters.Add(new SqlParameter("@TESTEE_ID", geresult.TESTEE_ID));
						sqlCommand.Parameters.Add(new SqlParameter("@SYS_ETY_DTM", geresult.SYS_ETY_DTM));
						sqlCommand.Parameters.Add(new SqlParameter("@TEST_CODE", geresult.TEST_CODE));
						sqlCommand.Parameters.Add(new SqlParameter("@COURSE_LEVEL_ID", geresult.COURSE_LEVEL_ID));
						sqlCommand.Parameters.Add(new SqlParameter("@KYOKA_CD", geresult.KYOKA_CD));
						sqlCommand.Parameters.Add(new SqlParameter("@UNIT_NO", geresult.UNIT_NO));
						sqlCommand.Parameters.Add(new SqlParameter("@ETY_ID_1", geresult.ETY_ID_1));
						sqlCommand.Parameters.Add(new SqlParameter("@RESULT1", geresult.RESULT1));
						sqlCommand.Parameters.Add(new SqlParameter("@ETY_ID_2", geresult.ETY_ID_2));
						sqlCommand.Parameters.Add(new SqlParameter("@RESULT2", geresult.RESULT2));
						sqlCommand.Parameters.Add(new SqlParameter("@SYS_UPD_ID", geresult.SYS_UPD_ID));
						sqlCommand.Parameters.Add(new SqlParameter("@RESULT", geresult.RESULT));
						sqlCommand.ExecuteNonQuery();
					}
					sqlTransaction.Commit();
				}
				catch (Exception)
				{

					sqlTransaction.Rollback();
					throw;
				}

			}
		}

		private void ManageFileImport_FormClosing(object sender, FormClosingEventArgs e)
		{
			if (t!=null)
			{
				t.Abort();
			}
		}

		private void UserSQLToolsImport(string filePath)
		{
			DeleteDBData();

			var msg = UserSQLTools(filePath);

			this.Invoke(new EventHandler(delegate
			{
				this.ShowRtxtMsg(msg);
				this.ShowMessage(ResourceCommon.GetResource("ImportSuccess"));
				ControlPage(false);
			}));

		}

		private void DeleteDBData()
		{
			int i = 0;
			string connectionString = ConfigurationManager.AppSettings["DbHelperConnectionStringReport"];
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				SqlTransaction sqlTransaction = connection.BeginTransaction();
				try
				{
					SqlCommand sqlCommand1 = new SqlCommand();
					sqlCommand1.Connection = connection;
					sqlCommand1.Transaction = sqlTransaction;
					sqlCommand1.CommandText = "TRUNCATE TABLE  GERESULT";

					sqlCommand1.CommandTimeout = 120;
					sqlCommand1.ExecuteNonQuery();
					
					sqlTransaction.Commit();
				}
				catch (Exception)
				{

					sqlTransaction.Rollback();
					throw;
				}
			}
		}
		private string UserSQLTools(string filePath)
		{
			Process p = new Process();
			p.StartInfo.FileName = "cmd.exe ";
			p.StartInfo.UseShellExecute = false;
			p.StartInfo.RedirectStandardInput = true;
			p.StartInfo.RedirectStandardOutput = true;
			p.StartInfo.RedirectStandardError = true;
			p.StartInfo.CreateNoWindow = true;
			p.StartInfo.WorkingDirectory = "c:\\";
			string outPut = null;
			string server = ConfigurationManager.AppSettings["Dbserver"]; //"dbtest02\\dev";
			string db = ConfigurationManager.AppSettings["Database"]; //"NMPDB_OLD.dbo.GERESULT";
			string userId = ConfigurationManager.AppSettings["DbuserId"]; //"nmpdb";
			string pwd = ConfigurationManager.AppSettings["Dbpwd"]; //"nmpdb";

            string strDilimiter = "";

            if (this.radioButton_TAB.Checked)
                strDilimiter = "\"\\t\"";
            if(this.radioButton_COMMA.Checked)
                strDilimiter = "\",\"";

            string command = "bcp " + "\"" + db + "\""
                               + " in \"" + filePath + "\""
                               + " -S \"" + server + "\""
                               + " -U \"" + userId + "\""
                               + " -P \"" + pwd + "\""
                //+" -F 2 "
                //+"-t \"\\t\""
                               + " -F " + this.textBox_FirstRow.Text + " "
                               + "-t " + strDilimiter
                               +" -c";
			
			p.StartInfo.Arguments = "/c " + command;

			p.Start();
			string msg = p.StandardOutput.ReadToEnd();        //输出流得到结果

			return msg;
		}

        private void ManageFileImport_Load(object sender, EventArgs e)
        {
            this.textBox_FirstRow.Text = "1";
            this.radioButton_TAB.Checked = true;
        }

        private void textBox_FirstRow_TextChanged(object sender, EventArgs e)
        {
            if(!Regex.IsMatch(this.textBox_FirstRow.Text,"[1-9]") && !"".Equals(this.textBox_FirstRow.Text))
                this.textBox_FirstRow.Text = "1";
        }

        private void button_showtext_Click(object sender, EventArgs e)
        {

            try
            {
                if (this.txtFilePath.Text.Trim() == "")
                {
                    MessageBox.Show(ResourceCommon.GetResource("PleasChangeFile"));
                    return;
                }
                StreamReader sr = null;
                try
                {
                    sr = File.OpenText(this.txtFilePath.Text.Trim());

                    this.rtxtMsg.Clear();
                    for (int i = 0; i < 10; i++)
                    {
                        this.rtxtMsg.AppendText(sr.ReadLine() + "\n");
                    }
                }
                catch(Exception err)
                {
                    throw err;
                }
                finally {
                    sr.Close();
                }
                //ControlPage(true);
                ////t = new Thread(delegate() { ImportDataProcess(this.txtFilePath.Text); });
                //t = new Thread(delegate() { UserSQLToolsImport(this.txtFilePath.Text); });
                //t.Start();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
                ControlPage(false);
            }
            
        }

        private void textBox_FirstRow_Leave(object sender, EventArgs e)
        {
            if ("".Equals(this.textBox_FirstRow.Text))
                this.textBox_FirstRow.Text = "1";
        }
	}

}

